CREATE OR REPLACE PROCEDURE SP_DELETE_CONSUMER
(
P_CONSUMER_KEY      IN        VARCHAR2,
P_USER_ID           IN        NUMBER,
P_USER_IS_ADMIN     IN        NUMBER, --0:NO; 1:YES
P_RESULT            OUT       NUMBER
)
AS
 
 --  Delete a consumer key.  This removes access to our site for all applications using this key.

BEGIN
P_RESULT := 0;

IF P_USER_IS_ADMIN = 1 THEN
 
  DELETE FROM OAUTH_SERVER_REGISTRY
	WHERE OSR_CONSUMER_KEY = P_CONSUMER_KEY
	AND (OSR_USA_ID_REF = P_USER_ID OR OSR_USA_ID_REF IS NULL);
 
ELSIF P_USER_IS_ADMIN = 0 THEN
 
  DELETE FROM OAUTH_SERVER_REGISTRY
	WHERE OSR_CONSUMER_KEY = P_CONSUMER_KEY
	AND OSR_USA_ID_REF   = P_USER_ID;
  
END IF;
     
EXCEPTION
WHEN OTHERS THEN
-- CALL THE FUNCTION TO LOG ERRORS
ROLLBACK;
P_RESULT := 1; -- ERROR
END;
/
